﻿
-----[dbo].spPerson-----

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].spPerson_Insert') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].spPerson_Insert
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].spPerson_Update') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].spPerson_Update
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].spPerson_Delete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].spPerson_Delete
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].spPerson_Get') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].spPerson_Get
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].spPerson_GetList') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].spPerson_GetList
GO


-----------------------------------------------------------------
CREATE PROCEDURE [dbo].spPerson_Insert
	@id int  OUTPUT 
	,@name nchar(100) 
AS


INSERT INTO Person (
	name
)
VALUES (
	@name
);

SET @id = SCOPE_IDENTITY()

GO			
-----------------------------------------------------------------
CREATE PROCEDURE [dbo].spPerson_Update
	@id int
	,@name nchar(100)
AS

UPDATE Person 
SET
	name = @name
WHERE 
	id = @id

GO		
-----------------------------------------------------------------
CREATE PROCEDURE [dbo].spPerson_Delete
	@id int
AS

DELETE  Person 
WHERE 
	id = @id
;

GO
-----------------------------------------------------------------
CREATE PROCEDURE [dbo].spPerson_GetList
	@OrderBy nvarchar(100)
	,@Page int
	,@PageSize int
	,@TotalRecords int OUTPUT

AS
	DECLARE @Sql nvarchar(1000);
	DECLARE @Start int;
	DECLARE @End int;

SELECT @TotalRecords = COUNT(1) FROM Person;
IF (@PageSize = 0) 
	BEGIN
		SET @Sql= 'SELECT * FROM Person ORDER BY ' + @OrderBy ;
		EXECUTE sp_executesql @Sql;
	END
ELSE
	BEGIN
		SET @Start = @Page * @PageSize + 1;
		SET @End = (@Page + 1) * @PageSize;
		SET @Sql = '
				SELECT * 
				FROM 
				(
					SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS ROWNUM 
					FROM  Person 
				) t
				WHERE @Start <= t.ROWNUM AND t.ROWNUM <= @End ' 
		EXECUTE sp_executesql @Sql, N'@Start int, @End int', @Start = @Start, @End = @End;
	END

GO			
-----------------------------------------------------------------
CREATE PROCEDURE [dbo].spPerson_Get
	 @id int

AS

SELECT * 
FROM Person
WHERE
	id = @id
;

GO

	